
package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Thomas
 */
public class DBCustomer implements IFDBCustomer 
{
    private Connection con;
    
    /** 
     * Creates a new instance of DBCustomer 
     */
    public DBCustomer() 
    {
      con = DbConnection1.getInstance().getDBcon();
    }
   
    
    //Implements the methods from the interface
    // get all employees
    @Override
    public ArrayList<Customer> getAllCustomers()
    {
        return miscWhere("");
    }
    
     //get one customer having the customerId
    @Override
    public Customer findCustomer(int customerId)
    {   
        String wClause = "  customerId = '" + customerId + "'";
        return singleWhere(wClause);
    }
    
     //find one person having the fname
    @Override
    public Customer searchCustomerFname(String fname)
    {
        String wClause = "fname like '%" + fname + "%'";
        System.out.println("SearchCustomer " + wClause);
        return singleWhere(wClause);
    }
    
     //find one customer having the lname
    @Override
    public Customer searchCustomerLname(String fname)
    {
        String wClause = "lname = '" + fname + "'";
        System.out.println("SearchCustomer " + wClause);
        return singleWhere(wClause);
    }
    
    //Insert one customer in the table
    @Override
     public int insertCustomer(Customer cust)
    { 
        int rc = -1;
        String query = "";
        //Club club = new Club();
        
        //call to get the next customerId number
        int nextCustomerId = GetMax.getMaxId("Select max(customerId) from Customer");
        nextCustomerId = nextCustomerId + 1;
        
        if(!zipCodeExists(cust.getZipCode()))
       {
           System.out.println(zipCodeExists(cust.getZipCode()));
           System.out.println(cust.getZipCode());
	   int zipCode = cust.getZipCode();
           String city = cust.getCity();
           insertZipCode(zipCode, city);
                    
       }
		
                if(cust instanceof Club)
                {
                   Club club = (Club) cust;
                    
                   query="INSERT INTO Customer(customerId, fname, lname, address, zipCode, phoneNo, email, clubName ,deliveryAddress, deliveryZipCode)" + "VALUES('"+
                   nextCustomerId  + "','"  +
                   cust.getFname() + "','"  +
                   cust.getLname() + "','"  +
                   cust.getAddress() + "','"  +
                   cust.getZipCode() + "','"  +
                   cust.getPhoneNo() + "','"  +
                   cust.getEmail()+"','" +
                   club.getClubName() + "','"  +
                   club.getDeliveryAddress() + "','"  +
                   club.getDeliveryZipCode()+"')";
                }
                else if (cust instanceof PrivateCustomer)
                {
                   query="INSERT INTO Customer(customerId, fname, lname, address, zipCode, phoneNo, email)" + "VALUES('"+
                   nextCustomerId  + "','"  +
                   cust.getFname() + "','"  +
                   cust.getLname() + "','"  +
                   cust.getAddress() + "','"  +
                   cust.getZipCode() + "','"  +
                   cust.getPhoneNo() + "','"  +
                   cust.getEmail()+"')";
                }
        	
       System.out.println(query);
	  	try{ // insert to person
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Insert exception in customer db: "+ex);
   	        }
                     
	   return(rc);
	}
    
    //Update a customer 
    @Override
    public int updateCustomer(Customer cust)
	{
            int rc=-1;
            String query = "";
            
            
            if(cust instanceof Club)
                {
                    Club club = (Club) cust;
                

                          query="UPDATE Customer SET "+
                          
		 	  "fname ='"+ cust.getFname()+"', "+
		 	  "lname ='"+ cust.getLname() + "', " +
                          "address ='"+ cust.getAddress() + "', " +
                          "zipCode ='"+ cust.getZipCode()+ "', " +
                          "phoneNo ='"+ cust.getPhoneNo() + "', " +
                          "email ='"+ cust.getEmail() + "', " +
                          "clubName ='"+ club.getClubName() + "', " +
                          "deliveryAddress ='"+ club.getDeliveryAddress() + "', " +
                          "deliveryZipCode ='"+ club.getDeliveryZipCode() + "' " +
                           
		          " WHERE customerId = '"+ cust.getCustomerId() + "'";
                System.out.println("Update query:" + query);
                }
                
            else if (cust instanceof PrivateCustomer)
            {
                PrivateCustomer pcust = (PrivateCustomer) cust;
                query ="UPDATE Customer SET "+
                          
		 	  "fname ='"+ cust.getFname()+"', "+
		 	  "lname ='"+ cust.getLname() + "', " +
                          "address ='"+ cust.getAddress() + "', " +
                          "zipCode ='"+ cust.getZipCode()+ "', " +
                          "phoneNo ='"+ cust.getPhoneNo() + "', " +
                          "email ='"+ cust.getEmail() + "' " +
                         
                        " WHERE customerId = '"+ cust.getCustomerId() + "'";
                System.out.println("Update query:" + query);
                
            }
                

                try{ // update customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in customer db: "+ex);
	  	}
		return(rc);
	}
    
    
    //delete a customer
    @Override
    public int deleteCustomer(int customerId)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM Customer WHERE customerId = '" +
				customerId + "'";
                System.out.println(query);
	  	try{ // delete from customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in Customer db: "+ex);
   	        }
		return(rc);
	}
    
    
    //private methods
    //michWere is used whenever we want to select more than one employee
    private ArrayList<Customer> miscWhere(String wClause)
	{
            ResultSet results;
	    ArrayList<Customer> list = new ArrayList<Customer>();	
		
	    String query =  buildQuery(wClause);
  
            try
            { 
                //read the customer from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
		int snr=0;
		while( results.next() )
                {
                    Customer cust = new Customer();
                    cust = buildCustomer(results);	
                    list.add(cust);
                }//end while
                
                stmt.close();
            }//slut try
            
            catch(Exception e)
            {
                System.out.println("Query exception - select: "+e);
			e.printStackTrace();
            }
            
            return list;	
	}
    
    //Singelwhere is used when we only select one customer 	
    private Customer singleWhere(String wClause)
	{
            ResultSet results;
	    Customer cust = new Customer();
                
	    String query =  buildQuery(wClause);
            System.out.println(query);
            try
            { // read the customer from the database
	 	Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 		
	 	if( results.next() )
                {
                    cust = buildCustomer(results);
                    stmt.close();
                }
                
                else
                {
                    //no customer was found
                    cust = null;
                }
            }//end try
            
            catch(Exception e)
            {
                System.out.println("Query exception: "+e);
            }
            
            return cust;
        }
    
    //method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT customerId, fname, lname, address, zipCode, phoneNo, email, clubName, deliveryAddress, deliveryZipCode FROM Customer";
		
		if (wClause.length()>0)
		query=query+" WHERE "+ wClause;
			
		return query;
	}
        
 
        //method to build an customer object
	private Customer buildCustomer(ResultSet results)
        {  
          
          Customer cust = new Customer();
          Club club = new Club();
          PrivateCustomer pcust = new PrivateCustomer();
          
          try
          { 
              // the columns from the table product  are used
              if(results.getString("clubName") != null)
              {
                  club.setClubName(results.getString("clubName"));
                  club.setDeliveryAddress(results.getString("deliveryAddress"));
                  club.setDeliveryZipCode(results.getInt("deliveryZipCode"));
                  cust = setSuperClass(club, results);
              }
              
              else
              {
                  cust = setSuperClass (pcust, results);
              }
          }

          catch(Exception e)
          {
              System.out.println("error in building the customer object1");
          }
                return cust;
          }


       public Customer setSuperClass(Customer cust, ResultSet results)
        {
            try
            {
                //create customer
                // the columns from the table customer are used
                cust.setCustomerId(results.getInt("customerId"));
                cust.setFname(results.getString("fname"));
                cust.setLname(results.getString("lname"));
                cust.setAddress(results.getString("address"));
                cust.setZipCode(results.getInt("zipCode"));
                cust.setPhoneNo(results.getString("phoneNo"));
                cust.setEmail(results.getString("email"));
            }
            catch(Exception e)
                    {
                        System.out.println("error in building the customer object2");
                    }
            return cust;
        }
        
       public boolean zipCodeExists(int zipCode)
      {
          String query="SELECT * FROM ZipCode";
          ResultSet results;
          int zip = 0;
          boolean found = false;
          try{ // read the zipcodes from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		while( results.next() ){
                            if(results.getInt("zipCode") == zipCode)
                            {
                                found = true;                           
                            }
                        }
                            stmt.close();
                            }//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
          return found;
      }
      
      public int insertZipCode(int zipCode, String city)
      {
          String query = "INSERT INTO ZipCode(zipCode, city)  VALUES('"+
                        zipCode  + "','"  +
                        city + "')";
          
          System.out.println("insert : " + query);
          
          int rc = -1;
          try{ // insert new zipcode
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try	
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in Customer db: "+ex);
	   }
	   return(rc);
      }
}